﻿Imports System.Data.OleDb

Public Class RecipeDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'If no querystring is passed, re-direct to the default page
        If Request.Params("ID") = "" Then Response.Redirect("Default.aspx")

        'Get the ID of the person submitting the rating from the Profile table and store it in the variable commenterID
        'Also use this process to know if the user has been blocked by an administrator from rating or commenting
        Dim raterID As String = ""
        Dim userBlocked As Boolean = False
        Dim cOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim cSqlString As String = "SELECT * FROM PROFILE WHERE USER_UNAME=@f1"
        Dim cCmd As OleDbCommand = New OleDbCommand(cSqlString, cOleDbConn)
        cCmd.CommandType = CommandType.Text
        cCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cOleDbConn.Open()
        Dim cDataReader As OleDbDataReader = cCmd.ExecuteReader
        If cDataReader.HasRows Then
            cDataReader.Read()
            If Not IsDBNull(cDataReader("PROFILE_ID")) Then raterID = cDataReader("PROFILE_ID")
            If Not IsDBNull(cDataReader("USER_BLCKD")) Then userBlocked = cDataReader("USER_BLCKD")
        End If

        'Check if the user has already rated this recipe so as to hide ability to rate it again
        Dim ROleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RSqlString As String = "SELECT * FROM RATING WHERE RECIPE_ID=@f1"
        Dim RCmd As OleDbCommand = New OleDbCommand(RSqlString, ROleDbConn)
        RCmd.CommandType = CommandType.Text
        RCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        ROleDbConn.Open()
        Dim RDataReader As OleDbDataReader = RCmd.ExecuteReader
        Dim userID As String = ""

        While RDataReader.Read()
            If Not IsDBNull(RDataReader("USER_ID")) Then
                userID = RDataReader("USER_ID")
                If userID = raterID Then
                    btn_ClickToRate.Visible = False
                Else
                    btn_ClickToRate.Visible = True
                End If
            End If

        End While

        'Default placeholder visibility settings
        plc_RateRecipe.Visible = False
        lblNotAllowedToRate.Visible = False
        lblSuccessRating.Visible = False

        'The below code serves to load the data in the comments gridview via code behind
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("RecipeList.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim SqlString As String = "SELECT * FROM PROFILE_COMMENT_VIEW WHERE REC_ID=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            oleDbConn.Open()

            Dim dataReader = cmd.ExecuteReader()
            lst_Comments.DataSource = dataReader
            lst_Comments.DataBind()

            'This part logs the year and month when this recipe was viewed
            Dim mmyy As String = Date.Today.ToString("yyyy/MM")

            Dim recipeViewedSQL As String = "INSERT into RECIPEVIEWED (USER_ID,RECIPE_ID,RECVIEW_MTHYR) Values (@f1,@f2,@f3)"
            Dim cmdRecipeViewed As OleDbCommand = New OleDbCommand(recipeViewedSQL, oleDbConn)
            cmdRecipeViewed.CommandType = CommandType.Text

            If User.Identity.Name = "" Then
                cmdRecipeViewed.Parameters.AddWithValue("@f1", "Not Registered")
            Else
                cmdRecipeViewed.Parameters.AddWithValue("@f1", User.Identity.Name)
            End If

            cmdRecipeViewed.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmdRecipeViewed.Parameters.AddWithValue("@f3", mmyy)
            cmdRecipeViewed.ExecuteNonQuery()

        End If

        'This part serves to turn on/off the visibility of the add comment placeholder
        'If the user is a registereduser and also not blocked by admin, then show the comments section
        If (User.IsInRole("registereduser") And (userBlocked = False)) Then
            ph_CommentLogin.Visible = False
            ph_CommentForm.Visible = True
            lbl_NotAllowedToComment.Visible = False
        Else
            If userBlocked = True Then
                lbl_NotAllowedToComment.Visible = True
                ph_CommentLogin.Visible = False
                ph_CommentForm.Visible = False
            Else
                lbl_NotAllowedToComment.Visible = False
                ph_CommentLogin.Visible = True
                ph_CommentForm.Visible = False
            End If
        End If

        'If the user is not a registered user, then do not show the click to rate button
        If (User.IsInRole("registereduser")) = False Then
            btn_ClickToRate.Visible = False
        End If

        'If the user has been blocked by the site administrator, then do not show the click to rate button
        If userBlocked = True Then
            btn_ClickToRate.Visible = False
            lblNotAllowedToRate.Visible = True
        End If


    End Sub

    Public Function getBigBaconStars(ByVal stars As Integer)
        'Given the number of stars, this function returns a string with the path to the relative image
        Dim retval As String = ""
        Select Case stars
            Case -1 To -0.99
                retval = "ui/0BigBacons.jpg"
            Case 0 To 0.99
                retval = "ui/1BigBacons.jpg"
            Case 1 To 1.99
                retval = "ui/2BigBacons.jpg"
            Case 2 To 2.99
                retval = "ui/3BigBacons.jpg"
            Case 3 To 3.99
                retval = "ui/4BigBacons.jpg"
            Case 4 To 4.99
                retval = "ui/5BigBacons.jpg"

        End Select
        Return retval
    End Function

    Protected Sub btn_AddComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddComment.Click

        'Get the ID of the commenter from the Profile table and store it in the variable commenterID
        Dim commenterID As String = ""
        Dim cOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim cSqlString As String = "SELECT * FROM PROFILE WHERE USER_UNAME=@f1"
        Dim cCmd As OleDbCommand = New OleDbCommand(cSqlString, cOleDbConn)
        cCmd.CommandType = CommandType.Text
        cCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cOleDbConn.Open()
        Dim cDataReader As OleDbDataReader = cCmd.ExecuteReader
        If cDataReader.HasRows Then
            cDataReader.Read()
            If Not IsDBNull(cDataReader("PROFILE_ID")) Then commenterID = cDataReader("PROFILE_ID")
        End If

        'Save the comment in the Comment Table
        Dim d As DateTime = Now
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim SqlString As String = "INSERT INTO COMMENT(PROFILE_ID,COMM_DESC,COMM_DATE,REC_ID) VALUES (@f1,@f2,@f3,@f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", commenterID)
        cmd.Parameters.AddWithValue("@f2", tb_CommComment.Text)
        cmd.Parameters.AddWithValue("@f3", d.ToString("yyyy-MM-dd"))
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'Populate the Comments gridview
        Dim fillComments As String = "SELECT * FROM PROFILE_COMMENT_VIEW WHERE REC_ID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        lst_Comments.DataSource = dataReader
        lst_Comments.DataBind()

        'The below code is used to clear the textfields after the user has submitted a comment
        tb_CommComment.Text = ""
    End Sub

    Protected Sub btn_ClickToRate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_ClickToRate.Click
        plc_RateRecipe.Visible = True
        btn_ClickToRate.Visible = False
    End Sub

    Protected Sub btn_SubmitRating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_SubmitRating.Click

        plc_RateRecipe.Visible = False
        btn_ClickToRate.Visible = True

        'Get the ID of the person submitting the rating from the Profile table and store it in the variable commenterID
        Dim commenterID As String = ""
        Dim cOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim cSqlString As String = "SELECT * FROM PROFILE WHERE USER_UNAME=@f1"
        Dim cCmd As OleDbCommand = New OleDbCommand(cSqlString, cOleDbConn)
        cCmd.CommandType = CommandType.Text
        cCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cOleDbConn.Open()
        Dim cDataReader As OleDbDataReader = cCmd.ExecuteReader
        If cDataReader.HasRows Then
            cDataReader.Read()
            If Not IsDBNull(cDataReader("PROFILE_ID")) Then commenterID = cDataReader("PROFILE_ID")
        End If

        'Save the rating in the Rating Table
        Dim d As DateTime = Now
        Dim RatOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RatSqlString As String = "INSERT INTO RATING(RATING_VALUE,RECIPE_ID,USER_ID,RATING_DATE) VALUES (@f1,@f2,@f3,@f4)"
        Dim RatCmd As OleDbCommand = New OleDbCommand(RatSqlString, RatOleDbConn)
        RatCmd.CommandType = CommandType.Text
        RatCmd.Parameters.AddWithValue("@f1", rtg_RateRecipe.CurrentRating)
        RatCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        RatCmd.Parameters.AddWithValue("@f3", commenterID)
        RatCmd.Parameters.AddWithValue("@f4", d.ToString("yyyy-MM-dd"))
        RatOleDbConn.Open()
        RatCmd.ExecuteNonQuery()
        RatOleDbConn.Close()
        RatOleDbConn.Dispose()
        btn_ClickToRate.Visible = False

        'Get the re-calculated average rating and total ratings
        Dim avgRating As String = ""
        Dim totalRating As String = ""
        Dim ViewOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim ViewSqlString As String = "SELECT * FROM STAT_TOTAL_AVG_RATING WHERE RECIPE_ID=@f1"
        Dim ViewCmd As OleDbCommand = New OleDbCommand(ViewSqlString, ViewOleDbConn)
        ViewCmd.CommandType = CommandType.Text
        ViewCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        ViewOleDbConn.Open()
        Dim ViewDataReader As OleDbDataReader = ViewCmd.ExecuteReader
        If ViewDataReader.HasRows Then
            ViewDataReader.Read()
            If Not IsDBNull(ViewDataReader("TotalRatings")) Then totalRating = ViewDataReader("TotalRatings")
            If Not IsDBNull(ViewDataReader("AverageRating")) Then avgRating = ViewDataReader("AverageRating")
        End If

        'Store these 2 values in the Recipe Table
        Dim RecOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RecSqlString As String = "UPDATE RECIPE SET REC_STARS=@f1,REC_STARSTIMES=@f2 WHERE REC_ID=@f3"
        Dim RecCmd As OleDbCommand = New OleDbCommand(RecSqlString, RecOleDbConn)
        RecCmd.CommandType = CommandType.Text
        RecCmd.Parameters.AddWithValue("@f1", avgRating)
        RecCmd.Parameters.AddWithValue("@f2", totalRating)
        RecCmd.Parameters.AddWithValue("@f3", Request.Params("ID"))
        RecOleDbConn.Open()
        RecCmd.ExecuteNonQuery()
        RecOleDbConn.Close()
        RecOleDbConn.Dispose()

        lblSuccessRating.Visible = True

    End Sub

End Class